﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Infragistics.Excel;
using System.Drawing;
using System.IO;

namespace VSM.Utils.Excel
{
    /// <summary>
    /// Export data to excel
    /// </summary>
    public class ExcelReport
    {
        #region Declare
        private IWorksheetCellFormat cellEmptyFormat = null;
        #endregion
        
        #region Property
        private int rowLimit = 1000;
        /// <summary>
        /// Gets or sets maximun row can working
        /// </summary>
        public int RowLimit
        {
            get { return rowLimit; }
            set { rowLimit = value; }
        }
        private int columnLimit = 100;
        /// <summary>
        /// Gets or sets maximun column can working
        /// </summary>
        public int ColumnLimit
        {
            get { return columnLimit; }
            set { columnLimit = value; }
        }
        private int maxLastRowNull = 10;
        /// <summary>
        /// Gets or sets maximun row allow null on last row
        /// This property is used to check last row index
        /// </summary>
        public int MaxLastRowNull
        {
            get { return maxLastRowNull; }
            set { maxLastRowNull = value; }
        }
        private Workbook book = null;
        /// <summary>
        /// Workbook
        /// </summary>
        public Workbook Workbook
        {
            get { return book; }
            set { book = value; }
        }
        private Worksheet activeSheet = null;
        /// <summary>
        /// Active sheet
        /// </summary>
        public Worksheet ActiveSheet
        {
            get { return activeSheet; }
            set { activeSheet = value; }
        }
        private string reportPath = "";
        /// <summary>
        /// Report path
        /// </summary>
        public string ReportPath
        {
            get { return reportPath; }
            set { reportPath = value; }
        }
        #endregion

        #region Constructor
        public ExcelReport()
        {
            string filePath = string.Empty; //Vnit.VnitResource.ResourceUtil.GetTempPath("", "xls");
            LoadWorkbook(filePath);
        }
        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="filePath"></param>
        public ExcelReport(string filePath)
        {
            LoadWorkbook(filePath);
        }
        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="filePath"></param>
        public ExcelReport(string templatePath, string filePath)
        {
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            File.Copy(templatePath, filePath);
            LoadWorkbook(filePath);
        }
        #endregion

        #region Other functions
        /// <summary>
        /// Load workbook from existed file to this instance
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public bool LoadWorkbook(string filePath)
        {
            try
            {
                FileInfo file = new FileInfo(filePath);
                if (file.Exists)
                {
                    file.IsReadOnly = false;
                    book = Workbook.Load(filePath);
                }
                else// If doesn't exist, create new one
                {
                    book = new Workbook(WorkbookFormat.Excel2007);
                    book.Worksheets.Add("Sheet1");
                    book.Save(filePath);
                }
                if (book != null)
                {
                    if (book.Worksheets.Count > 0)
                    {
                        activeSheet = book.Worksheets[0];
                    }
                    reportPath = filePath;
                    cellEmptyFormat = book.CreateNewWorksheetCellFormat();
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        #endregion

        #region Merge cell
        /// <summary>
        /// Get WorksheetCell
        /// </summary>
        /// <returns></returns>
        public bool MergeCells(Point cell1, Point cell2)
        {
            try
            {
                activeSheet.MergedCellsRegions.Add(cell1.X, cell1.Y, cell2.X, cell2.Y);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
                /// <summary>
        /// Get WorksheetCell
        /// </summary>
        /// <returns></returns>
        public bool MergeCellsWithBorder(Point cell1, Point cell2)
        {
            return MergeCellsWithBorder(cell1, cell2, Color.Black);
        }
        /// <summary>
        /// Get WorksheetCell
        /// </summary>
        /// <returns></returns>
        public bool MergeCellsWithBorder(Point cell1, Point cell2, Color borderColor)
        {
            try
            {
                activeSheet.MergedCellsRegions.Add(cell1.X, cell1.Y, cell2.X, cell2.Y);
                SetBorder(cell1, cell2, borderColor);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        /// <summary>
        /// Get WorksheetCell
        /// </summary>
        /// <returns></returns>
        public bool MergeCells(string address1, string address2)
        {
            try
            {
                WorksheetCell cell1 = activeSheet.GetCell(address1);
                WorksheetCell cell2 = activeSheet.GetCell(address2);
                activeSheet.MergedCellsRegions.Add(cell1.RowIndex, cell1.ColumnIndex, cell2.RowIndex, cell2.ColumnIndex);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        #endregion

        #region Get cell/value
        /// <summary>
        /// Get WorksheetCell
        /// </summary>
        /// <returns></returns>
        public WorksheetCell GetCell(int row, int col)
        {
            try
            {
                return activeSheet.Rows[row].Cells[col];
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Get WorksheetCell
        /// </summary>
        /// <returns></returns>
        public WorksheetCell GetCell(string address)
        {
            
            try
            {
                return activeSheet.GetCell(address);
            }
            catch (Exception ex)
            {
                
                return null;
            }
        }
        /// <summary>
        /// Set new value into one cell (by cell index)
        /// </summary>
        /// <returns></returns>
        public object GetValue(int row, int col, object value)
        {
            
            try
            {
                return activeSheet.Rows[row].Cells[col].Value;
            }
            catch (Exception ex)
            {
                
                return null;
            }
        }
        /// <summary>
        /// Set new value into one cell (by cell address string)
        /// </summary>
        /// <returns></returns>
        public bool SetValue(string address, object value)
        {
            
            try
            {
                //for(int i=0;i<activeSheet.Max
                WorksheetCell cell = activeSheet.GetCell(address);
                cell.Value = value;
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Set new value into one cell (by cell position)
        /// </summary>
        /// <returns></returns>
        public bool SetValue(Point position, object value)
        {
            
            try
            {
                activeSheet.Rows[position.X].Cells[position.Y].Value = value;
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Set new value into one cell (by cell index)
        /// </summary>
        /// <returns></returns>
        public bool SetValue(int row, int col, object value)
        {
            
            try
            {
                activeSheet.Rows[row].Cells[col].Value = value;
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        #endregion

        #region Set wordwrap
                public bool SetColumnWordwrap(int col, bool wwrap)
        {
            
            try
            {
                activeSheet.Columns[col].CellFormat.WrapText = wwrap ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False;
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Set row word wrap
        /// </summary>
        /// <returns></returns>
        public bool SetRowWordwrap(int row, bool wwrap)
        {
            
            try
            {
                activeSheet.Rows[row].CellFormat.WrapText = wwrap ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False;
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Set cell word wrap
        /// </summary>
        /// <returns></returns>
        public bool SetCellWordwrap(int row, int col, bool wwrap)
        {
            
            try
            {
                activeSheet.Rows[row].Cells[col].CellFormat.WrapText = wwrap ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False;
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        #endregion

        #region Set width
        /// <summary>
        /// Set column width
        /// </summary>
        /// <returns></returns>
        public bool SetColumnWidth(int col, int width)
        {
            
            try
            {
                activeSheet.Columns[col].Width = width;
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Set column word wrap
        /// </summary>
        /// <returns></returns>

        /// <summary>
        /// Set row height
        /// </summary>
        /// <returns></returns>
        public bool SetRowHeight(int row, int height)
        {
            
            try
            {
                //activeSheet.Columns[row].Width = height;//2011/01/12 Thi deleted
                activeSheet.Rows[row].Height = height;//2011/01/12 Thi add
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Set active sheet
        /// </summary>
        /// <param name="sheetIndex"></param>
        /// <returns></returns>
        public bool SetActiveSheet(int sheetIndex)
        {
            
            try
            {
                activeSheet = book.Worksheets[sheetIndex];

                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        #endregion

        #region Set border
        /// <summary>
        /// Set border for one cell
        /// </summary>
        public bool SetBorder(Point cell, Color color)
        {
            return SetBorder(cell, cell, color);
        }
        /// <summary>
        /// Set border for range
        /// </summary>
        public bool SetBorder(Point fromPoint, Point toPoint, Color color)
        {
            
            try
            {
                for (int i = fromPoint.X; i <= toPoint.X; i++)
                {
                    for (int j = fromPoint.Y; j <= toPoint.Y; j++)
                    {
                        //Left
                        activeSheet.Rows[i].Cells[j].CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin;
                        activeSheet.Rows[i].Cells[j].CellFormat.LeftBorderColor = color;
                        //Top
                        activeSheet.Rows[i].Cells[j].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                        activeSheet.Rows[i].Cells[j].CellFormat.TopBorderColor = color;
                        //Right
                        activeSheet.Rows[i].Cells[j].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;
                        activeSheet.Rows[i].Cells[j].CellFormat.RightBorderColor = color;
                        //Bottom
                        activeSheet.Rows[i].Cells[j].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                        activeSheet.Rows[i].Cells[j].CellFormat.BottomBorderColor = color;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        //2011/01/12 Thi add start
        /// <summary>
        /// Set border outside for range
        /// </summary>
        public bool SetBorderOutside(Point fromPoint, Point toPoint, Color color)
        {
            
            try
            {
                for (int i = fromPoint.X; i <= toPoint.X; i++)
                {             
                    //Left
                    activeSheet.Rows[i].Cells[fromPoint.Y].CellFormat.LeftBorderStyle = CellBorderLineStyle.Thin;
                    activeSheet.Rows[i].Cells[fromPoint.Y].CellFormat.LeftBorderColor = color;                                        
                    //Right
                    activeSheet.Rows[i].Cells[toPoint.Y].CellFormat.RightBorderStyle = CellBorderLineStyle.Thin;
                    activeSheet.Rows[i].Cells[toPoint.Y].CellFormat.RightBorderColor = color;                  
                }                
                for (int i = fromPoint.Y; i <= toPoint.Y; i++)
                {
                    //Top
                    activeSheet.Rows[fromPoint.X].Cells[i].CellFormat.TopBorderStyle = CellBorderLineStyle.Thin;
                    activeSheet.Rows[fromPoint.X].Cells[i].CellFormat.TopBorderColor = color;
                    //Bottom
                    activeSheet.Rows[toPoint.X].Cells[i].CellFormat.BottomBorderStyle = CellBorderLineStyle.Thin;
                    activeSheet.Rows[toPoint.X].Cells[i].CellFormat.BottomBorderColor = color;
                }                              
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        //2011/01/12 Thi add end
        /// <summary>
        /// Set border for range
        /// </summary>
        public bool SetLeftBottomRightBorder(Point cell, Color color)
        {
            
            try
            {
                int i = cell.X;
                int j = cell.Y;
                //Left
                activeSheet.Rows[i].Cells[j].CellFormat.LeftBorderStyle = CellBorderLineStyle.Default;
                activeSheet.Rows[i].Cells[j].CellFormat.LeftBorderColor = color;
                //Right
                activeSheet.Rows[i].Cells[j].CellFormat.RightBorderStyle = CellBorderLineStyle.Default;
                activeSheet.Rows[i].Cells[j].CellFormat.RightBorderColor = color;
                //Bottom
                activeSheet.Rows[i].Cells[j].CellFormat.BottomBorderStyle = CellBorderLineStyle.Default;
                activeSheet.Rows[i].Cells[j].CellFormat.BottomBorderColor = color;
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Set left border for one cell
        /// </summary>
        public bool SetLeftBorder(Point cell, Color color)
        {
            return SetLeftBorder(cell, cell, color);
        }
        /// <summary>
        /// Set left border for range
        /// </summary>
        public bool SetLeftBorder(Point fromPoint, Point toPoint, Color color)
        {
            
            try
            {
                for (int i = fromPoint.X; i <= toPoint.X; i++)
                {
                    for (int j = fromPoint.Y; j <= toPoint.Y; j++)
                    {
                        activeSheet.Rows[i].Cells[j].CellFormat.LeftBorderStyle = CellBorderLineStyle.Default;
                        activeSheet.Rows[i].Cells[j].CellFormat.LeftBorderColor = color;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Set top border for one cell
        /// </summary>
        public bool SetTopBorder(Point cell, Color color)
        {
            return SetTopBorder(cell, cell, color);
        }
        /// <summary>
        /// Set top border for range
        /// </summary>
        public bool SetTopBorder(Point fromPoint, Point toPoint, Color color)
        {
            
            try
            {
                for (int i = fromPoint.X; i <= toPoint.X; i++)
                {
                    for (int j = fromPoint.Y; j <= toPoint.Y; j++)
                    {
                        activeSheet.Rows[i].Cells[j].CellFormat.TopBorderStyle = CellBorderLineStyle.Default;
                        activeSheet.Rows[i].Cells[j].CellFormat.TopBorderColor = color;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Set right border for one cell
        /// </summary>
        public bool SetRightBorder(Point cell, Color color)
        {
            return SetRightBorder(cell, cell, color);
        }
        /// <summary>
        /// Set right border for range
        /// </summary>
        public bool SetRightBorder(Point fromPoint, Point toPoint, Color color)
        {
            
            try
            {
                for (int i = fromPoint.X; i <= toPoint.X; i++)
                {
                    for (int j = fromPoint.Y; j <= toPoint.Y; j++)
                    {
                        activeSheet.Rows[i].Cells[j].CellFormat.RightBorderStyle = CellBorderLineStyle.Default;
                        activeSheet.Rows[i].Cells[j].CellFormat.RightBorderColor = color;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Set bottom border for one cell
        /// </summary>
        public bool SetBottomBorder(Point cell, Color color)
        {
            return SetBottomBorder(cell, cell, color);
        }
        /// <summary>
        /// Set bottom border for range
        /// </summary>
        public bool SetBottomBorder(Point fromPoint, Point toPoint, Color color)
        {
            
            try
            {
                for (int i = fromPoint.X; i <= toPoint.X; i++)
                {
                    for (int j = fromPoint.Y; j <= toPoint.Y; j++)
                    {
                        activeSheet.Rows[i].Cells[j].CellFormat.BottomBorderStyle = CellBorderLineStyle.Default;
                        activeSheet.Rows[i].Cells[j].CellFormat.BottomBorderColor = color;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        #endregion

        #region Set font
        /// <summary>
        /// Set border for range
        /// </summary>
        public bool SetFont(Point fromPoint, Point toPoint, Font font)
        {
            
            try
            {
                for (int i = fromPoint.X; i <= toPoint.X; i++)
                {
                    for (int j = fromPoint.Y; j <= toPoint.Y; j++)
                    {
                        IWorkbookFont cellfont = activeSheet.Rows[i].Cells[j].CellFormat.Font;
                        cellfont.Bold = font.Bold ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False;
                        cellfont.Height = (int)(font.Size * 20);

                        cellfont.Italic = font.Italic ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False;
                        cellfont.Name = font.Name;
                        cellfont.Strikeout = font.Strikeout ? ExcelDefaultableBoolean.True : ExcelDefaultableBoolean.False;
                        cellfont.UnderlineStyle = font.Underline ? FontUnderlineStyle.Single : FontUnderlineStyle.None;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        #endregion

        #region Set color
        
        /// <summary>
        /// Set border for one cell
        /// </summary>
        public bool SetForeColor(Point position, Color color)
        {
            return SetForeColor(position, position, color);
        }
        /// <summary>
        /// Set border for range
        /// </summary>
        public bool SetForeColor(Point fromPoint, Point toPoint, Color color)
        {
            
            try
            {
                for (int i = fromPoint.X; i <= toPoint.X; i++)
                {
                    for (int j = fromPoint.Y; j <= toPoint.Y; j++)
                    {
                        IWorkbookFont cellfont = activeSheet.Rows[i].Cells[j].CellFormat.Font;
                        cellfont.Color = color;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Set border for one cell
        /// </summary>
        public bool SetBackColor(Point cellPosition, Color color)
        {
            return SetBackColor(cellPosition, cellPosition, color);
        }
        /// <summary>
        /// Set border for range
        /// </summary>
        public bool SetBackColor(Point fromPoint, Point toPoint, Color color)
        {
            
            try
            {
                for (int i = fromPoint.X; i <= toPoint.X; i++)
                {
                    for (int j = fromPoint.Y; j <= toPoint.Y; j++)
                    {
                        activeSheet.Rows[i].Cells[j].CellFormat.FillPattern = FillPatternStyle.Solid;
                        activeSheet.Rows[i].Cells[j].CellFormat.FillPatternForegroundColor = color;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        #endregion

        #region Save
        /// <summary>
        /// Save workbook to file
        /// </summary>
        /// <returns></returns>
        public bool Save()
        {
            
            try
            {
                book.Save(reportPath);
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        #endregion

        #region Dispose
        /// <summary>
        /// Free resource
        /// </summary>
        public void Dispose()
        {
            book = null;
            activeSheet = null;
        }
        #endregion

        #region Insert/copy row
        /// <summary>
        /// Insert one row at before index
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public bool InsertRow(int index)
        {
            return InsertRow(index, 1);
        }
        /// <summary>
        /// Insert count rows at before index
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public bool InsertRow(int index, int count)
        {
            
            try
            {
                int lastRow = GetLastRow();
                if (lastRow > rowLimit) lastRow = rowLimit - 1;
                //Copy row down
                if (lastRow > -1)
                {
                    for (int i = lastRow; i >= index; i--)
                    {
                        if (!CopyRow(i, i + count))
                        {
                            return false;
                        }
                    }
                    for (int i = 0; i < count; i++)
                    {
                        //Set null row
                        if (!SetNullRow(index + i, false))
                        {
                            return false;
                        }
                        //Set format for new row
                        if (!CopyRowFormat(index + count, index + i))
                        {
                            return false;
                        }
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Get last row on current worksheet
        /// </summary>
        /// <param name="from"></param>
        /// <returns></returns>
        public int GetLastRow()
        {
            try
            {
                int countNullRow = 0;
                int i = 0;
                for (i = 0; i < rowLimit; i++)
                {
                    if (IsNullRow(i))
                    {
                        countNullRow++;
                    }
                    else
                    {
                        countNullRow = 0;
                    }
                    if (countNullRow == maxLastRowNull)
                    {
                        break;
                    }
                }
                int lastRow = i - countNullRow;
                return lastRow;
            }
            catch (Exception ex)
            {
                
                return -1;
            }
        }
        /// <summary>
        /// Set all cells in rows is null value
        /// </summary>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <returns></returns>
        public bool SetNullRow(int index, bool clearFormat)
        {
            try
            {
                for (int j = 0; j < columnLimit; j++)
                {
                    activeSheet.Rows[index].Cells[j].Value = null;
                    if (clearFormat)
                    {
                        activeSheet.Rows[index].Cells[j].CellFormat.SetFormatting(cellEmptyFormat);
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Check to indicate whether input row is empty row
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public bool IsNullRow(int index)
        {
            
            try
            {
                for (int i = 0; i < columnLimit; i++)
                {
                    if (activeSheet.Rows[index].Cells[i].Value != null)
                    {
                        return false;
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Copy row data and format from one position to other position
        /// </summary>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <returns></returns>
        public bool CopyRow(int from, int to)
        {
            try
            {
                for (int j = 0; j < columnLimit; j++)
                {
                    //Copy format
                    activeSheet.Rows[to].Cells[j].CellFormat.SetFormatting(activeSheet.Rows[from].Cells[j].CellFormat);
                    //Copy value
                    activeSheet.Rows[to].Cells[j].Value = activeSheet.Rows[from].Cells[j].Value;
                }
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        /// <summary>
        /// Copy row format from one position to other position
        /// </summary>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <returns></returns>
        public bool CopyRowFormat(int from, int to)
        {
            try
            {
                for (int j = 0; j < columnLimit; j++)
                {
                    //Copy format
                    activeSheet.Rows[to].Cells[j].CellFormat.SetFormatting(activeSheet.Rows[from].Cells[j].CellFormat);
                }
                return true;
            }
            catch (Exception ex)
            {
                
                return false;
            }
        }
        #endregion
    }
}
